package com.whnk.job.task;

import com.whnk.job.util.CommonUtils;
import com.whnk.job.util.ConnectionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.*;
import java.text.MessageFormat;

/**
 * 定时任务基础服务---执行存储过程，类中方法，一般情况下，不用修改
 * 
 * @author whnk
 */
public abstract class BaseTask {
    private final Logger logger = LoggerFactory.getLogger(BaseTask.class);

    @Autowired
    ConnectionUtil connectionUtil;

    protected String procedureName = "";
    private String custId = "";
    protected String modelId = "";
    
    
    
    /**
         * 获取存储过程调用语句
     * @return
     */
    protected abstract String getProcedureInfo();

    /**
     * 获取存储过程出参数据，前提是，readExecutorResult返回true
     * @throws SQLException
     */
    protected abstract void getResultInfo(CallableStatement csInAndOut) throws SQLException;
    
 
    /**
     * 
     * @Title: doModelScript   
     * @Description: 通过模型ID在def_t_mode_info表中查询对应的模型脚本（即存储过程函数名）
     * 					并执行，同时插入日志表：FLW_T_SCHEDULE_RUN_STATUS
     * @param: @param modelId      
     * @return: void      
     * @throws
     */
    protected void doModelScript(String modelId) {
        this.modelId = modelId;
        DBInfo dbInfo = new DBInfo();
        try {
            openConnection(dbInfo);
            prepareSqlStatement(dbInfo);
            getModelScriptInfo(dbInfo);
           // logBatchRunInfo(dbInfo.insertStartLog);
            runProcedure(dbInfo);
           // logBatchRunInfo(dbInfo.insertEndLog);
        } catch (Exception e) {
            logger.error("模型运行失败,模型ID为:" + modelId, e);
        } finally {
            releaseConnection(dbInfo);
        }
    }

    /**
     * 
     * @Title: runProcedure   
     * @Description: 调用存储过程，包括模型开始运行的日志，调用存储过程，设置存储过程出入参以及存储过程出参取值。
     * @param: @param dbInfo
     * @param: @throws Exception      
     * @return: void      
     * @throws
     */
    private void runProcedure(DBInfo dbInfo) throws Exception {
        String procedureInfo = getProcedureInfo();
        dbInfo.csInAndOut = dbInfo.connectionInAndOut.prepareCall(procedureInfo);
        setInAndOutParam(dbInfo.csInAndOut);
        dbInfo.csInAndOut.execute();
        if (readExecutorResult()) {
            getResultInfo(dbInfo.csInAndOut);
        }
    }

    /**
     * 打开数据库连接
     * @param dbInfo
     */
    private void openConnection(DBInfo dbInfo) {
        dbInfo.connectionInAndOut = connectionUtil.getConnection();
        if (dbInfo.connectionInAndOut == null) {
            logger.error("获取数据库连接失败");
            throw new RuntimeException("获取数据库连接失败");
        }
    }

    /**
     * 日志数据库句柄
     * @param dbInfo
     */
    private void releaseConnection(DBInfo dbInfo) {
        CommonUtils.CloseHandle(dbInfo.queryProcedureNameByModelId);
        CommonUtils.CloseHandle(dbInfo.insertStartLog);
        CommonUtils.CloseHandle(dbInfo.insertEndLog);
        CommonUtils.CloseHandle(dbInfo.rsInAndOut);
        CommonUtils.CloseHandle(dbInfo.csInAndOut);
        CommonUtils.CloseHandle(dbInfo.connectionInAndOut);
    }

    /**
     * 预编译sql，提高效率：查询模型，插入日志
     * @param dbInfo
     */
    private void prepareSqlStatement(DBInfo dbInfo) {
        try {
            dbInfo.queryProcedureNameByModelIdSql = "select t.MODEL_SCRIPT as modelScript, t.cust_id as custID from def_T_ModelInfo t where t.MODEL_ID = ?";
            //dbInfo.insertStartLogSql = "insert into FLW_T_SCHEDULE_RUN_STATUS(CUST_ID, MODEL_ID, EXECUTE_STATUS, EXECUTE_START_TIME) values(?, ?, ?, current_timestamp())";
            dbInfo.insertStartLogSql = "insert into FLW_T_SCHEDULE_RUN_STATUS(CUST_ID, MODEL_ID, EXECUTE_STATUS, EXECUTE_START_TIME, EXECUTE_END_TIME) values(?, ?, ?, current_timestamp(), current_timestamp())";
            dbInfo.insertEndLogSql = "update FLW_T_SCHEDULE_RUN_STATUS set EXECUTE_STATUS = '1', EXECUTE_END_TIME = current_timestamp() where cust_id = ? and model_id = ? and EXECUTE_STATUS = ?";
            dbInfo.queryProcedureNameByModelId = dbInfo.connectionInAndOut.prepareStatement(dbInfo.queryProcedureNameByModelIdSql);
            dbInfo.insertStartLog = dbInfo.connectionInAndOut.prepareStatement(dbInfo.insertStartLogSql);
            dbInfo.insertEndLog = dbInfo.connectionInAndOut.prepareStatement(dbInfo.insertEndLogSql);
        } catch (SQLException e) {
            logger.error("预编译语句失败", e);
            throw new RuntimeException("prepare statement failed");
        }
    }

    /**
     * 数据库查询模型对应存储过程
     * @param dbInfo
     */
    private void getModelScriptInfo(DBInfo dbInfo) {
        try {
            dbInfo.insertStartLog.setString(1,custId);
            dbInfo.insertStartLog.setString(2,modelId);
            dbInfo.insertStartLog.setString(3,"0");
            dbInfo.insertStartLog.execute();
            dbInfo.queryProcedureNameByModelId.setString(1, modelId);
            dbInfo.queryProcedureNameByModelId.execute();
            ResultSet resultSet = dbInfo.queryProcedureNameByModelId.getResultSet();
            while (resultSet.next()) {
                procedureName = resultSet.getString("modelScript");
                custId = resultSet.getString("custID");
            }
            logger.info(MessageFormat.format("modelId:{0}, custId:{1}, procedureName:{2}", modelId, custId, procedureName));
            dbInfo.insertEndLog.setString(1,custId);
            dbInfo.insertEndLog.setString(2,modelId);
            dbInfo.insertEndLog.setString(3,"0");
            dbInfo.insertEndLog.execute();
        } catch (SQLException e) {
            logger.error(MessageFormat.format("查询模型失败，模型ID为{0},返回存储过程名称为{1}, 客户编号为{2}",
                    modelId, procedureName, custId), e);
            throw new RuntimeException("对应模型不存在，请检查参数是否正确");
        }
    }


    /**
     * 钩子函数，如果需要读取存储过程返回值，需要重写该方法，并设置为true
     * @return
     */
    protected boolean readExecutorResult() {
        return false;
    }

    /**
     * 设置和注册存储过程入参和出参信息
     * @throws SQLException
     */
    protected void setInAndOutParam(CallableStatement csInAndOut) throws SQLException {}

    /**
     * 写入数据库操作日志
     * @param preparedStatement
     * @throws SQLException
     */
    private void logBatchRunInfo(PreparedStatement preparedStatement) throws SQLException {
        preparedStatement.setString(1, custId);
        preparedStatement.setString(2, modelId);
        preparedStatement.setString(3, "0");
        preparedStatement.execute();
    }

    private static class DBInfo {
        public Connection connectionInAndOut;
        public CallableStatement csInAndOut;
        public ResultSet rsInAndOut;
        public PreparedStatement queryProcedureNameByModelId;
        public PreparedStatement insertStartLog;
        public PreparedStatement insertEndLog;
        public String queryProcedureNameByModelIdSql;
        public String insertStartLogSql;
        public String insertEndLogSql;
    }
}
